import openpyxl
import pymysql

# 建立mysql链接
db = pymysql.connect(host='gz-cdb-o43ukrr9.sql.tencentcdb.com',
                       port=60652,
                       user='dskj',
                       passwd='Dskj!2019',
                       db = 'bss-gq',
                       charset='utf8' )
cursor = db.cursor()# 使用cursor()方法获取操作游标
# 打开excel文件,获取工作簿对象
wb = openpyxl.load_workbook("C:\\Users\\wangle\\Desktop\\x9e\\新增车型\\AM8\\AM8_异动报警.xlsx")
ws = wb.active  # 当前活跃的表单

vehicle_ser = "7"
vehicle_model_id = "20"
number = 1
item={}
for i in range(2, 48):
    item['AM8'] = ws.cell(row=i, column=3).value
    number = number + 1
    if item['AM8'] in '×':
        continue
    item['alarm_id'] = vehicle_ser+vehicle_model_id+"%03d" %number
    item['alarm_code'] = ws.cell(row=i, column=1).value
    item['alarm_name'] = ws.cell(row=i, column=2).value.replace(" ", "")
    item['vehicle_series_id'] = vehicle_ser
    print(item)
    into = "insert into `tab_alarm` (`alarm_id`, `alarm_code`, `alarm_name`, `vehicle_series_id`) values(%s,%s,%s,%s)"
    values = (item['alarm_id'],item['alarm_code'],item['alarm_name'],item['vehicle_series_id'])
    cursor.execute(into, values)
    db.commit()
        
